/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import Entity.TourPackage;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author MrKoi
 */
public class TourPackageBean {
    Connect con=new Connect();
    ResultSet rs;
    Connection conn;
    Collection getAllTourPackage,getIdTourPackage,listtour;
    Collection getPriceTourPackge;
    PreparedStatement pre;
    Collection getSeachTourName;
    Collection getSearchPriceTour;
    CallableStatement cl=null;
    public TourPackageBean() {
    }
    private void closeCnn(){
        try {
            cl.close();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(AdminBean.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public boolean CreateTourPackage(String TourID,String Image,String TourName,String Duration,float Adultprice,float Childprice,String Description,String State) {
        conn=con.getConnection();
        try {
            cl=conn.prepareCall("{call CreateTourPackage_sp(?,?,?,?,?,?,?,?)}");
            cl.setString(1,TourID);
            cl.setString(2,Image);
            cl.setString(3,TourName);
            cl.setString(4,Duration);
            cl.setFloat(5,Adultprice);
            cl.setFloat(6,Childprice);
            cl.setString(7,Description);
            cl.setString(8,State);
            int result=cl.executeUpdate();
            if(result>0) {
                return true;
            }
            closeCnn();
            
        } catch(SQLException ex) {
            closeCnn();
            return false;
        }
        return false;
    }
    
    public Collection getTourPackgeId(String idtour) {
        conn=con.getConnection();
        try {
            getIdTourPackage=new ArrayList();
            cl=conn.prepareCall("{call getTourPackgeId_sp(?)}");
            cl.setString(1,idtour);
            rs=cl.executeQuery();
            while(rs.next()) {
                TourPackage tourPack=new TourPackage();
                
                tourPack.setTourID(rs.getString("TourID"));
                tourPack.setImage(rs.getString("Image"));
                tourPack.setTourName(rs.getString("TourName"));
                tourPack.setDuration(rs.getString("Duration"));
                tourPack.setAdultPrice(rs.getFloat("Adultprice"));
                tourPack.setChildPrice(rs.getFloat("Childprice"));
                tourPack.setDescription(rs.getString("Description"));
                tourPack.setState(rs.getString("State"));
                getIdTourPackage.add((Object)tourPack);
            }
            closeCnn();
        } catch (Exception ex) {
            closeCnn();
            ex.printStackTrace();
        }
        return getIdTourPackage;
    }
    //search tour
    public Collection searchtour(String nametour,String pricefrom,String priceto) {
        conn=con.getConnection();
        listtour=new ArrayList();
        try {
            
            //String sql="select TourID,Image,TourName,convert(nvarchar(15),Duration,103)as'Duration',Adultprice,Childprice,Description,State from TourPackages order by TourID desc";
            cl=conn.prepareCall("{call searchtour_sp(?,?,?)}");
            cl.setFloat("priceFrom",Float.parseFloat(pricefrom));
            cl.setFloat("priceTo",Float.parseFloat(priceto));
            cl.setString("TourName", nametour);
            ResultSet rs=cl.executeQuery();
            while(rs.next()) {
                TourPackage tourPackage=new TourPackage();
                
                tourPackage.setTourID(rs.getString("TourID"));
                tourPackage.setImage(rs.getString("Image"));
                tourPackage.setTourName(rs.getString("TourName"));
                tourPackage.setDuration(rs.getString("Duration"));
                tourPackage.setAdultPrice(rs.getFloat("Adultprice"));
                tourPackage.setChildPrice(rs.getFloat("Childprice"));
                tourPackage.setDescription(rs.getString("Description"));
                tourPackage.setState(rs.getString("State"));
                listtour.add((Object)tourPackage);
            }
            closeCnn();
        }catch (Exception ex){
            closeCnn();
        }
        return listtour;
    }
    //lay tat ca danh sach
    public Collection getAllTourPackage() {
        conn=con.getConnection();
        getAllTourPackage=new ArrayList();
        try {
            
            cl=conn.prepareCall("{call getAllTourPackage_sp}");
            ResultSet rs=cl.executeQuery();
            while(rs.next()) {
                TourPackage tourPackage=new TourPackage();
                
                tourPackage.setTourID(rs.getString("TourID"));
                tourPackage.setImage(rs.getString("Image"));
                tourPackage.setTourName(rs.getString("TourName"));
                tourPackage.setDuration(rs.getString("Duration"));
                tourPackage.setAdultPrice(rs.getFloat("Adultprice"));
                tourPackage.setChildPrice(rs.getFloat("Childprice"));
                tourPackage.setDescription(rs.getString("Description"));
                tourPackage.setState(rs.getString("State"));
                getAllTourPackage.add((Object)tourPackage);
            }
            closeCnn();
        }catch (Exception ex){
            closeCnn();
        }
        return getAllTourPackage;
    }
    
    
    public boolean deleteTourPackage(String tourPackageId) {
        conn=con.getConnection();
        int row;
        boolean check=true;
        try {
            cl=conn.prepareCall("{call deleteTourPackage_sp(?)}");
            cl.setString("tourpId", tourPackageId);
          int rs=cl.executeUpdate();
          if(rs>0){
              closeCnn();
              return  true;
          }else{
              closeCnn();
              return false;
          }
            
        } catch (Exception ex) {
            return false;
        }
    }
    
    public boolean UpdateTourPackage(String idTourPackage,String tourName,String Duration,float Adultprice,float Childprice,String Description, String state) {
        conn=con.getConnection();
        try {
            cl=conn.prepareCall("{call UpdateTourPackage_sp(?,?,?,?,?,?,?)}");
            cl.setString("TourName",tourName);
            cl.setString("Duration",Duration);
            cl.setFloat("Adultprice",Adultprice);
            cl.setFloat("Childprice",Childprice);
            cl.setString("Description",Description);
            cl.setString("State",state);
            cl.setString("TourID",idTourPackage);
            int row=cl.executeUpdate();
            if(row>0)
                return true;
            closeCnn();
        } catch (Exception ex) {
            closeCnn();
            return false;
        } return false;
    }
    
    public boolean UpdateImagePackTour(String idTourPackage,String image) {
        conn=con.getConnection();
        try{
            cl=conn.prepareCall("{call UpdateImagePackTour_sp(?,?)}");
            cl.setString("image",image);
            cl.setString("TourID",idTourPackage);
            int row=cl.executeUpdate();
            if(row>0)
                return true;
            closeCnn();
        }catch (Exception ex) {
            closeCnn();
            return false;
        }
        return false;
    }
    public Collection getPricetourPackage(String idtour) {
        conn=con.getConnection();
        getPriceTourPackge=new ArrayList();
        
        try {
            cl=conn.prepareCall("{call getPricetourPackage_sp(?)}");
            cl.setString(1,idtour);
            rs=cl.executeQuery();
            while(rs.next()) {
                TourPackage tourPackage=new TourPackage();
                tourPackage.setAdultPrice(rs.getFloat("Adultprice"));
                tourPackage.setChildPrice(rs.getFloat("Childprice"));
                getPriceTourPackge.add(tourPackage);
            }  closeCnn();
        } catch (Exception ex) {
             closeCnn();
            ex.printStackTrace();
        }return getPriceTourPackge;
    }
    
    public Collection SearchTourName(String nameTour) {
        conn=con.getConnection();
        getSeachTourName=new ArrayList();
        try {
            cl=conn.prepareCall("{call SearchTourName_sp(?)}");
            cl.setString("TourName", nameTour);
            rs=cl.executeQuery();
            while(rs.next()) {
                TourPackage tourPack=new TourPackage();
                tourPack.setTourID(rs.getString("TourID"));
                tourPack.setImage(rs.getString("Image"));
                tourPack.setTourName(rs.getString("TourName"));
                tourPack.setDuration(rs.getString("Duration"));
                tourPack.setAdultPrice(rs.getFloat("Adultprice"));
                tourPack.setChildPrice(rs.getFloat("Childprice"));
                tourPack.setDescription(rs.getString("Description"));
                tourPack.setState(rs.getString("State"));
                getSeachTourName.add(tourPack);
            }
            closeCnn();
        } catch (Exception ex) {
            closeCnn();
            ex.printStackTrace();
        }return getSeachTourName;
        
    }
    public Collection SearchTourByPrice(String price) {
        conn=con.getConnection();
        getSearchPriceTour=new ArrayList();
        try {
            cl=conn.prepareCall("{call SearchTourByPrice_sp(?)}");
           cl.setFloat("price",Float.valueOf(price));
            rs=cl.executeQuery();
            while(rs.next()) {
                TourPackage tourPack=new TourPackage();
                tourPack.setTourID(rs.getString("TourID"));
                tourPack.setImage(rs.getString("Image"));
                tourPack.setTourName(rs.getString("TourName"));
                tourPack.setDuration(rs.getString("Duration"));
                tourPack.setAdultPrice(rs.getFloat("Adultprice"));
                tourPack.setChildPrice(rs.getFloat("Childprice"));
                tourPack.setDescription(rs.getString("Description"));
                tourPack.setState(rs.getString("State"));
                getSearchPriceTour.add(tourPack);
            } closeCnn();
        }catch (Exception ex) {
            closeCnn();
            ex.printStackTrace();
        }return getSearchPriceTour;
    }
    
    public static void main(String[] ar){
        TourPackageBean obj=new TourPackageBean();
        Collection cl= obj.getAllTourPackage();
                
    }
}
